#!/usr/bin/env python
#coding=utf-8
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relation, backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey,create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import join
from sqlalchemy.sql import func


#Building a Relation
#关系是双向绑定的
Base = declarative_base()
metadata = MetaData()
engine = create_engine('sqlite:///SimpleSite/development.db', echo=True)

#定义表的结构 
addresses_table = Table('addresses', metadata,
     Column('id', Integer, primary_key=True),
     Column('email_address', String),
     Column('user_id',Integer),
)

#创建表
metadata.create_all(engine) 



class Address(Base):
     __tablename__ = 'addresses'
     id = Column(Integer, primary_key=True)
     email_address = Column(String, nullable=False)
     user_id = Column(Integer, ForeignKey('users.id'))

     #

     def __init__(self, email_address):
         self.email_address = email_address

     def __repr__(self):
         return "<Address('%s')>" % self.email_address


class User(Base):
     __tablename__ = 'users'

     id = Column(Integer, primary_key=True)
     name = Column(String)
     fullname = Column(String)
     password = Column(String)
     
     def __init__(self, name, fullname, password):
         self.name = name
         self.fullname = fullname
         self.password = password

     def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)

users_table = User.__table__
addresses_table = Address.__table__

#建立关系
user = relation(User, backref=backref('addresses', order_by=id))
User.addresses = relation(Address, primaryjoin=Address.user_id==User.id)
#增加一个USER
jack = User('jack', 'Jack Bean', 'gjffdd')
#给此USER 增加两个EMAIL
jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
#
Session = sessionmaker(bind=engine)
session=Session()

#提交数据
#session.add(jack)
#session.commit()

#Querying with Joins,不使用JOIN 
for u, a in session.query(User, Address).filter(User.id==Address.user_id).filter(Address.email_address=='jack@google.com').all():  
    print u,a 
#Querying with Joins,
for u  in session.query(User).select_from(join(User, Address)).filter(Address.email_address=='jack@google.com').all():
    print u


#Using Subqueries
stmt = session.query(Address.user_id, func.count('*').label('address_count')).group_by(Address.user_id).subquery()
for u, count in session.query(User, stmt.c.address_count).outerjoin((stmt, User.id==stmt.c.user_id)).order_by(User.id):
    print u, count

#级联删除

session.close()  # roll back and close the transaction
from sqlalchemy.orm import clear_mappers
from sqlalchemy.orm import mapper
clear_mappers() # clear mappers


#配置级联删除
mapper(User, users_table, properties={    
     'addresses':relation(Address, backref='user', cascade="all, delete, delete-orphan")
})
mapper(Address, addresses_table)
#要删除的JACK
jack = session.query(User).get(9)
session.delete(jack)
session.commit()
print session.query(User).filter_by(name='jack').count() 
